package org.novelhibernate.util;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.novelhibernate.core.ParameterMapper;
import org.novelhibernate.core.annotation.PojoForeignEntityDeclaration;
import org.novelhibernate.exception.InproperDivisionOfSQLException;
import org.novelhibernate.exception.NovelhibernateException;

/**  
 * This is the core SQL parser, which parse all incoming sqls, analysis what
 * attribute is static and what should be extracted from the target object, it
 * is based on recognising alias, therefore any attributes that needs to be
 * extracted from target object, has to been start with the alias of target + .,
 * the rest of the sql would be parsed as static parameters or
 * relationship-alias parameters.
 * 
 * If the operation is delete, then a restriction is needed when parsing the
 * delete sql, because delete sql does not allow to use alias, therefore it is
 * need to identify which attributes needs to be extract from target object,
 * which is not.
 * 
 * DBMS internal function, xx in (?,?,?), as stated in TransactionImpl, would
 * not be recognised thus they can't be extracted from target object
 * 
 * @author Tao Chen
 * 
 */
public class SQLParser {
	
	static {
		
		// create a singleton instance when start up
		parser = new SQLParser();
		
	}
	
	private static SQLParser parser;
	
	
	private SQLParser (){
		
	}
	
	public static SQLParser getInstance(){
		
		return parser;
	}
	
	/**
	 * Retrieve all aliases that defined in the root select statement
	 * @param sql
	 * @return
	 */
	public String parseJoinQuery (String sql) {
		
		Pattern pattern = Pattern.compile("select.+?from\\s+",Pattern.CASE_INSENSITIVE);
		
		Matcher matcher = pattern.matcher(sql);
		
		String afterFilter = "";
		
		while (matcher.find()) {

			afterFilter = matcher.group().substring(6, matcher.group().length() - 5);
			break;
		}
		
		String[] filter = afterFilter.split(",");
		StringBuffer buffer = new StringBuffer();
		for (int i = 0; i < filter.length; i++){
			
			// Filter 'as'
			buffer.append(filter[i].split("\\s+as\\s+")[0].trim());
			
			if (i != filter.length - 1)
				buffer.append(",");
			
		}
		
		return buffer.toString();
	}

	public String[] parseInsertSQL(String sql, Class<?> clazz) throws NovelhibernateException{
		
		if (null == clazz)
			throw new NovelhibernateException("The entity class for SQL " + sql
					+ " can not be NULL");

		Pattern pattern = Pattern.compile("insert\\s+into.+?values\\s+",
				Pattern.CASE_INSENSITIVE);

		Matcher matcher = pattern.matcher(sql);

		String afterFilter = "";

		while (matcher.find()) {

			afterFilter = matcher.group().replaceAll("insert\\s+into", "")
					.replace("values", "");
			break;
		}

		if ("".equals(afterFilter.trim()))
			return null;
		else {

			String[] results = afterFilter.trim().split(",");

			for (int i = 0; i < results.length; i++) {

				try {

					clazz.getDeclaredField(results[i]);

				} catch (NoSuchFieldException e) {

					boolean isError = true;
					// Examine if a error
					for (java.lang.reflect.Field field : clazz
							.getDeclaredFields()) {

						// If this is a FK
						if (field.isAnnotationPresent(PojoForeignEntityDeclaration.class)
								&& results[i].equals(field.getAnnotation(
										PojoForeignEntityDeclaration.class)
										.columnName())) {

							// Form the format as 'category.id'
							results[i] = field.getName()
									+ "."
									+ field.getAnnotation(
											PojoForeignEntityDeclaration.class)
											.externalEntityColumnName();
							isError = false;
							break;
						}
					}
					// Otherwise throw exception
					if (isError)
						throw new NovelhibernateException("The require attribute " + results[i] + " can not be found in "
										+ clazz.getName());

				}

			}

			return results;
		}

	}
	
	
	public Object[] parseUpdateSQL(String sql, Class<?> clazz) throws NovelhibernateException, InproperDivisionOfSQLException{

		
		String[] parameters = null;
		
		
		Pattern pattern = Pattern.compile("update.+?set\\s+", Pattern.CASE_INSENSITIVE);
		// filter where statement
		String afterFilter = sql.split("\\s+where\\s+")[0];
		String alias = "";
		Matcher matcher = pattern.matcher(afterFilter);
		List<ParameterMapper> results = new ArrayList<ParameterMapper>();

		while (matcher.find()) {

			// If the root target table has been assigned an alias
			if (matcher.group().split("\\s+as\\s+").length == 1)
				throw new InproperDivisionOfSQLException();
			
			alias = matcher.group().replaceAll("\\s+set", "").split("\\s+as\\s+")[1].trim();
			// filter update ... set
			afterFilter = afterFilter.replace(matcher.group(), "").trim();
			break;
		}

		String[] parameterSections = afterFilter.split(",");

        // index of parameter
	    int index = 0;
	    
	    String value;
		// extract the names of variable that defined in 'set'
		for (int i = 0; i < parameterSections.length; i++){
			
			
			if(-1 != parameterSections[i].indexOf("?")){
				
			  // for comment call DBMS internal functions, ignore here	
			  // other like number = number + ? are also being ignored	
			  // then they needs to define by user manually 	
			  if(parameterSections[i].replaceAll("\\s*", "").endsWith("=?")){
				  
				value = parameterSections[i].replace(alias + ".", "").split("=")[0].trim();
				
				// examine if this is FK
				if(clazz != null){
					
					try {
						
						clazz.getDeclaredField(value);
						results.add(new ParameterMapper(value, index));
						
					} catch (NoSuchFieldException e) {
						
						boolean isError = true;
						// Examine if a error 
						for (java.lang.reflect.Field field : clazz.getDeclaredFields()){
							
							// If this is a FK
							if(field.isAnnotationPresent(PojoForeignEntityDeclaration.class) &&
									value.equals(field.getAnnotation(PojoForeignEntityDeclaration.class).columnName())){
								
								// Form the format as 'category.id'
								results.add(new ParameterMapper(field.getName() + "." + field.getAnnotation(
										       PojoForeignEntityDeclaration.class).externalEntityColumnName(), index));
								isError = false;
								break;
							}
						}
                        // Otherwise throw exception						
						if(isError)
							throw new NovelhibernateException("The require attribute " + value + " can not be found in " 
									+ clazz.getName());
						
					}
				} else
					results.add(new ParameterMapper(value, index));
				
			  } else {
				  
				  index = countQuestionMark(parameterSections[i], index);
				  continue;
			  }
				  
				  
				
				index++;
			}
		}
		
		index--;
		
		pattern = Pattern.compile("update.+?where\\s+", Pattern.CASE_INSENSITIVE);

		matcher = pattern.matcher(sql);

		
		String whereClouse = "";
		while (matcher.find()) {

			whereClouse = sql.replace(matcher.group(),"").replaceAll("select.+?where\\s+", "");
			
			parameters = whereClouse.split("\\s+and\\s+");
			break;
		}

		
		String[] nextedParameters;
		

		if (null != parameters) {
			// extract the names of variable
			for (int i = 0; i < parameters.length; i++) {

				nextedParameters = parameters[i].split("\\s+or\\s+");
			
				 for (int j = 0; j < nextedParameters.length; j++) 				 
					index = addParameters(results, nextedParameters[j], index, alias);
				 

				
			}
		
	
		}
		
		
		if (clazz == null && results.size() != 0)
			throw new NovelhibernateException("The related class is null but the sql requires value from it");

		return results.toArray();
	}
	
	
	public Object[] parseDeleteSQL(String sql, List<Integer> restrict) throws InproperDivisionOfSQLException{


		String[] parameters = null;

		Pattern pattern = Pattern.compile("delete.+?where\\s+", Pattern.CASE_INSENSITIVE);

		Matcher matcher = pattern.matcher(sql);


		
		// the where statement
		String whereClouse;

		while (matcher.find()) {

		
			whereClouse = sql.replace(matcher.group(),"").replaceAll("select.+?where\\s+", "");
			
			parameters = whereClouse.split("\\s+and\\s+");
			break;
		}

		List<ParameterMapper> results = new ArrayList<ParameterMapper>();
		String[] nextedParameters;
		

		int index = -1;
		
		if (null != parameters) {
			// extract the names of variable
			for (int i = 0; i < parameters.length; i++) {

				nextedParameters = parameters[i].split("\\s+or\\s+");
			
				 for (int j = 0; j < nextedParameters.length; j++) 				 
						index = addParameters(results, nextedParameters[j], index, restrict);
	                

				
			}
		
	
		}

		return results.toArray();

	}

	private int addParameters(List<ParameterMapper> results, String element, int index, String alias){
	
		
		String[] exmain;
		
		// if no parameter needed
		if (element.indexOf("?") == -1)
			return index;
		// if exist in (?,?,?)... and internal function
		else if (element.replaceAll("\\s*", "").endsWith(")"))
			return countQuestionMark(element, index);
	    // if the criteria is not for the target table			
	    else if (!element.trim().startsWith(alias + ".") || element.split("\\(").length > 1)
			return index + 1;
	
		else if ((exmain = element.split("\\s+like\\s+")).length > 1){}
		else if ((exmain = element.split("!=")).length > 1){}
		else if ((exmain = element.split("<=")).length > 1){}
		else if ((exmain = element.split(">=")).length > 1){}
		else if ((exmain = element.split("=")).length > 1){}
		else if ((exmain = element.split(">")).length > 1){}
	    else if ((exmain = element.split("<")).length > 1){}
		
		index++;
		
		// filter nested criteria
		String[] splitNested = exmain[0].split("\\(");
		String param = (splitNested.length == 1)? exmain[0].replace(alias + ".", "").trim() : splitNested[splitNested.length - 1].replace(alias + ".", "").trim();
		results.add(new ParameterMapper(param, index));
		
		return index;
	}
	
	private int addParameters(List<ParameterMapper> results, String element, int index, List<Integer> restrict){
	
		
		String[] exmain;
		
		// if no parameter needed
		if (element.indexOf("?") == -1)
			return index;
		// if exist in (?,?,?)... and internal function
		else if (element.replaceAll("\\s*", "").endsWith(")"))
			return countQuestionMark(element, index);	
	    else if (null != restrict && restrict.contains(index + 1))
	    	return index + 1;
		else if ((exmain = element.split("\\s+like\\s+")).length > 1){}
		else if ((exmain = element.split("!=")).length > 1){}
		else if ((exmain = element.split("<=")).length > 1){}
		else if ((exmain = element.split(">=")).length > 1){}
		else if ((exmain = element.split("=")).length > 1){}
		else if ((exmain = element.split(">")).length > 1){}
	    else if ((exmain = element.split("<")).length > 1){}
		
		index++;
		
		// filter nested criteria
		String[] splitNested = exmain[0].split("\\(");
		String param = (splitNested.length == 1)? exmain[0].trim() : splitNested[splitNested.length - 1].trim();
		results.add(new ParameterMapper(param, index));
		
		return index;
	}
	
	private int countQuestionMark (String source, int index){
		
		Pattern pattern = Pattern.compile("\\?");
 
		Matcher matcher = pattern.matcher(source);
		
		while (matcher.find())
			index++;
		
		return index;
		
		
	}

}
